﻿Imports System.Data.OleDb
'add com "Microsoft Excel 11.0 Object Library"  
Imports Excel=Microsoft.Office.Interop.Excel   
Imports System.Reflection   
Public Class Form1
    Private saleDS As DataSet1
    Dim conn As OleDbConnection
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        saleDS = New DataSet1
        conn = New OleDbConnection(connStr)
        FillSalesDataset()
        Me.DataGridView1.DataSource = saleDS.Order

        For Each col As DataGridViewTextBoxColumn In Me.DataGridView1.Columns
            Debug.Print(col.HeaderText)
        Next
        Dim cmb As New DataGridViewComboBoxColumn()
        cmb.HeaderText = "Select Data"
        cmb.Name = "cmb"
        cmb.MaxDropDownItems = 4
        cmb.DataSource = saleDS.Product
        cmb.DisplayMember = "ProductName"
        cmb.ValueMember = "ProductID"
        Me.DataGridView1.Columns.Add(cmb)
    End Sub

    Public Sub FillSalesDataset()
        FillProductTable()
        FillEmployeeTable()
        FillOrderTable()
    End Sub

    Public Sub FillProductTable() 
        Dim command As New OleDbCommand("select * from product", conn)
        Dim adp As New OleDbDataAdapter(command)
        adp.Fill(saleDS.Product)
    End Sub

    Public Sub FillEmployeeTable() 
        Dim command As New OleDbCommand("select * from Employee", conn)
        Dim adp As New OleDbDataAdapter(command)
        adp.Fill(saleDS.Employee)
    End Sub

    Public Sub FillOrderTable()
        Dim command As New OleDbCommand("select * from [Order]", conn)
        Dim adp As New OleDbDataAdapter(command)
        adp.Fill(saleDS.Order)
    End Sub

    Public Const connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\Learning\SVNWorking\datagridviewTest\Sales.mdb"

     
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
         Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer 

        xlApp = New Excel.Application 
        xlWorkBook = xlApp.Workbooks.Add(misValue) 
        xlWorkSheet = xlWorkBook.Sheets("sheet1") 

        For Each col As DataGridViewColumn In DataGridView1.Columns
            xlWorkSheet.Cells(1, col.Index + 1) = col.HeaderText.ToString
        Next

        For i = 2 To DataGridView1.Rows.Count - 1
            For j = 0 To DataGridView1.ColumnCount - 1
                Dim vv As String
                If DataGridView1(j, i).Value Is Nothing Then
                    vv = "Niet ingevuld"
                Else
                    vv = DataGridView1(j, i).Value.ToString
                End If
                xlWorkSheet.Cells(i + 1, j + 1) = vv
            Next 
        Next 

        xlWorkBook.Activate()
        xlWorkBook.SaveAs("F:\Learning\SVNWorking\datagridviewTest\export.xls") 
        xlWorkBook.Close()
        xlApp.Quit()

        MsgBox("You can find your report at " & "F:\Learning\SVNWorking\datagridviewTest\export.xls")


    End Sub
End Class
